Creating table with LIKE or SELECT Clause and CONSTRAINTS behavior?

My question is particularly about the approach we use to create a clone or more specifically copy of the table with LIKE operator or with the help of SELECT clause also copy all the constraints from the old table?

    CREATE TABLE newTable LIKE oldTable;

OR

    CREATE TABLE newTable AS (SELECT * FROM oldTable) WITH NO DATA;

Please explain if these above statements will create the exact working clone from the oldTable or it will just create the structure from the oldTable for newTable.

When I'm saying clone I expect that it should have to create a new working copy of the oldTable, which will have all the constraints applied. For example for my TIMESTAMP column which have a definition as shown below.

    LAST_UPDATE_TS TIMESTAMP NOT NULL GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP;

Does my newTable's LAST_UPDATE_TS will work as it was working in the oldTable?

There are several cases and types of constraints we can have for each column so applying all those manually later or after creating table as we seen above, doesn't remain as a good option. Please explain. Thanks :)

Answers


As far as I understood, how about this ?

SELECT TMP.* INTO DESTINATION_TABLE
FROM
(SELECT * FROM SOURCE_TABLE WHERE 1=0) AS TMP

This works & creates new empty table structure from existing table structure.

Now about the constraints, we will go through following steps,

1]

create table pk (id int primary key);

2]

sp_help pk

3]

select * into fk from (select * from pk) as tmp

4]

sp_help fk


Need Your Help

In .htaccess, how do you 301-redirect dynamic pages?

php .htaccess redirect dynamic

In .htaccess, how do you 301-redirect dynamic pages?

Program received signal 0 for uiimagepickercontroller

iphone uiimageview uiimage uiimagepickercontroller

I have an app where user can take picture from camera and image is displayed in imageview.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.